﻿/*
* 修改表结构
*/

if exists(
	select 1 from sys.all_objects A,sys.all_columns B
	where A.object_id=B.object_id and A.name='T_Supplier' and B.name = 'SupplierTypeId'
)
 begin
	print '字段【SupplierTypeId】已存在'
 end
else
 begin
	alter table T_Supplier add SupplierTypeId int
 end

if exists(
	select 1 from sys.all_objects A,sys.all_columns B
	where A.object_id=B.object_id and A.name='T_Supplier' and B.name = 'Gender'
)
 begin
	print '字段【Gender】已存在'
 end
else
 begin
	alter table T_Supplier add Gender int
	update T_Supplier set Gender=1
 end

if exists(
	select 1 from sys.all_objects A,sys.all_columns B
	where A.object_id=B.object_id and A.name='T_Supplier' and B.name = 'Wangwang'
)
 begin
	print '字段【Wangwang】已存在'
 end
else
 begin
	alter table T_Supplier add Wangwang varchar(100)
 end

/*
* 新建视图
*/
if exists (select * from sysobjects where id = object_id(N'[dbo].[View_T_Supplier]') and OBJECTPROPERTY(id, N'IsView') = 1)
	drop view [dbo].[View_T_Supplier]

GO
CREATE VIEW [dbo].[View_T_Supplier]
AS

select 
T_Supplier.* 
,SupplierTypeName=SupplierType.ClassificationName
from T_Supplier
left outer join T_Classification as SupplierType 
	on ParentId=(select top 1 ClassificationId from T_Classification where ClassificationCode='SupplierType')
		and SupplierType.ClassificationId = T_Supplier.SupplierTypeId 

GO

/*
* 添加菜单
*/
if not exists(select 1 from Sys_Module where [ModuleCode]='004')
 begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('供应商信息','004',0,1,'',0,'03','','001')
 end
else
 begin
	print '【供应商信息】菜单模块已存在'
 end

declare @NewModuleId int;

/*----------------------------------------------
供应商管理
----------------------------------------------*/
select @NewModuleId=ModuleId from Sys_Module where [ModuleCode]='004'

if not exists(select 1 from Sys_Module where [ModuleCode]='002003Add')
 begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('供应商资料录入','002003Add',@NewModuleId,1,'/Phone/SupplierEdit.aspx?Action=HAdd',1,'01','','001')
 end
else
 begin
	print '【供应商资料录入】菜单模块已存在'
 end

if not exists(select 1 from Sys_Module where [ModuleCode]='004001')
 begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('供应商类型管理','004001',@NewModuleId,1,'/Phone/Classification.aspx?Code=SupplierType',1,'03','','001')
 end
else
 begin
	print '【供应商类型管理】已存在'
 end

update Sys_Module set 
[Href]='/Phone/SupplierEdit.aspx?Action=HAdd'
,[ModuleName]='供应商资料录入'
,[OrderNo]='01'
,[ParentId]=@NewModuleId
where [ModuleCode]='002003Add'

update Sys_Module set 
[Href]='/Phone/SupplierList.aspx'
,[ModuleName]='供应商资料管理'
,[OrderNo]='02'
,[ParentId]=@NewModuleId
where [ModuleCode]='002003'

/*----------------------------------------------
客户资料
----------------------------------------------*/
select @NewModuleId=ModuleId from Sys_Module where [ModuleCode]='003'

update Sys_Module set 
[Href]='/Customer/CustomerEdit.aspx?Action=HAdd'
,[ModuleName]='客户资料录入'
,[OrderNo]='01'
,[ParentId]=@NewModuleId
where [ModuleCode]='003001Add'

update Sys_Module set 
[Href]='/Customer/CustomerList.aspx'
,[ModuleName]='客户资料管理'
,[OrderNo]='02'
,[ParentId]=@NewModuleId
where [ModuleCode]='003001'

update Sys_Module set 
[Href]='/Customer/CustomerQuery.aspx'
,[ModuleName]='客户资料高级查询'
,[OrderNo]='03'
,[ParentId]=@NewModuleId
where [ModuleCode]='003001Super'

update Sys_Module set 
[Href]='/Phone/Classification.aspx?Code=RecordType'
,[ModuleName]='记录类型管理'
,[OrderNo]='04'
,[ParentId]=@NewModuleId
where [ModuleCode]='003002'

update Sys_Module set 
[Href]='/Phone/Classification.aspx?Code=ProgressType'
,[ModuleName]='进度类型管理'
,[OrderNo]='05'
,[ParentId]=@NewModuleId
where [ModuleCode]='003003'

/*----------------------------------------------
添加类型
----------------------------------------------*/
if exists(select 1 from T_Classification where ClassificationCode='SupplierType')
 begin
    print '【供应商类型】已存在'
 end
else
 begin
	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(0,'SupplierType','供应商类型')
 end

